<?php
require_once $ubi['SERVER']['SERVER_ROOT'] . '/dao/dbConnection.php';
require_once $ubi['SERVER']['SERVER_ROOT'] . '/dao/daoAddress.php';
require_once $ubi['SERVER']['SERVER_ROOT'] . '/dao/daoResource.php';

require_once $ubi['SERVER']['SERVER_ROOT'] . '/model/provider.php';
require_once $ubi['SERVER']['SERVER_ROOT'] . '/model/providerDTO.php';

require_once $ubi['SERVER']['SERVER_ROOT'] . '/utility/generalFunctions.php';

class ProviderDao {
	private $_db;
	
	public function __construct() {
		$this->_db = new DbConnection();
	}
	
	/**
	 *
	 * Add a provider to the database
	 * @param $provider: an instance of ProviderModel object
	 * returns ProviderId if add successfully, otherwise returns 0
	 */
	public function addProvider($provider) {		
		$query = sprintf("CALL sp_AddProvider('%s','%u','%s','%s', '%s', '%u', '%s', '%s', '%s', '%s', '%s', '%u', '%u', '%u', '%u', @ProviderId)", 
					$provider->getProvider(),  
					$provider->getUserID(),
					$provider->getDescription(),
					$provider->getCellPhone(),
					$provider->getTelephone(),
					$provider->getAddressID(),
					$provider->getCategoryList(),
					$provider->getResourceList(),
					$provider->getAnswerList(),
					$provider->getWebsite(),
					$provider->getEmail(),
					$provider->getCreatedDate(),
					$provider->getAccessedDate(),
					$provider->getModifiedDate(),
					$provider->getMapResourceID());
		$result = $this->_db->executeQuery($query);
		
		$result = $this->_db->executeQuery("SELECT @ProviderId");
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();		    
		}
		
		if ($row = mysql_fetch_assoc($result)) {
			$id = $row['@ProviderId'];			
		}		
		
		return $id;
	}
	
	/**
	 * 
	 * Get the provider with the ProviderId passed in
	 * @param unknown_type $providerid
	 * returns ProviderModel if the provider exists, otherwise returns null
	 */
	public function getProvider($providerID) {
		$query = sprintf("SELECT * FROM providers WHERE ProviderId = '%u'", $providerID);
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();		    
		}
		
		if ($row = mysql_fetch_assoc($result)) {
			$provider = $this->getProviderFromFetchRow($row);						
		}
		
		mysql_free_result($result);
		
		return $provider;
	}
	
	//get a list of providers by user id
	//returns an array of providers record
	public function getProvidersByUserId($userID) {
		$query = sprintf("SELECT * FROM providers WHERE UserId = '%u'", $userID);
		$result = $this->_db->executeQuery($query);
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();		    
		}
		
		$providers = array();
		
		while($row = mysql_fetch_assoc($result)) {
			$provider = $this->getProviderFromFetchRow($row);			
			$providers[] = $provider;
		}		
		
		mysql_free_result($result);
						
		return $providers;
	}
	
	//get number of providers by user id
	//returns number of provider of the user
	public function getNumberOfProvidersByUserID($userID) {
		$query = sprintf("SELECT COUNT(*) FROM providers WHERE UserId = '%u'", $userID);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();
			return;
		}
		
		if ($row = mysql_fetch_row($result)) {						
			return $row[0];
		}
	}
	
	public function getProvidersByUserIDFromTo($userID, $from, $offset) {
		$query = sprintf("SELECT * FROM providers WHERE UserId = '%u' ORDER BY CreatedDate DESC LIMIT %u, %u", $userID, $from, $offset);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
			echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		$providers = array();
		
		while($row = mysql_fetch_assoc($result)) {
			$provider = $this->getProviderFromFetchRow($row);			
			$providers[] = $provider;
		}		
		
		mysql_free_result($result);
						
		return $providers;
	}
	
	public function addResource($providerID, $resourceID) {
		$query = sprintf("CALL sp_AddResourceToProvider('%u', '%s')", $providerID, $resourceID);
		
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;				
	}
	
	public function searchProvider($searchText) {
		$query = "SELECT * FROM providers WHERE provider LIKE '%" . 
			GeneralFunctions::stripString(trim($searchText)) . "%'";
		$result = $this->_db->executeQuery($query);
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();
		    return;
		}

		$providerList = array();
		
		while ($row = mysql_fetch_assoc($result)) {					
			$provider = $this->getProviderFromFetchRow($row);	
			$providerList[] = $provider;			
		}
		
		mysql_free_result($result);

		return $providerList;		
	}

	public function updateProvider($provider) {
		$query = sprintf("CALL sp_UpdateProvider('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%d')", 
					$provider->getProvider(), $provider->getDescription(), $provider->getCellphone(),
					$provider->getTelephone(), $provider->getCategoryList(), $provider->getAnswerList(), 
					$provider->getWebsite(), $provider->getProviderID());
					
		$result = $this->_db->executeQuery($query);
	
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}
		
		$numRows = mysql_affected_rows();
		
		return $numRows;
	}
	
	public function incFrequency($providerID) {
		$query = sprintf("UPDATE providers SET Frequency = Frequency + 1 WHERE ProviderId = '%d'", $providerID);
		$this->_db->executeQuery($query);
	}

	public function getNumberOfComments($providerID) {
		global $ubi;
		
		$query = sprintf("SELECT COUNT(*) FROM comments WHERE IndicteeId = '%d' AND InteractType = '%d'", 
					$providerID, $ubi['PROVIDER']['VALUE']);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}

		$row = mysql_fetch_array($result);			
		$n = $row[0];		
		
		return $n;
	}
	
	public function getNumberOfFavourites($providerID) {
		global $ubi;
		
		$query = sprintf("SELECT COUNT(*) FROM favourites WHERE IndicteeId = '%d' AND InteractType = '%d'", 
					$providerID, $ubi['PROVIDER']['VALUE']);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}

		$row = mysql_fetch_array($result);			
		$n = $row[0];		
		
		return $n;
	}
	
	public function getProviderMaxFrequency() {
		$query = "SELECT * FROM providers WHERE Frequency =(select max(Frequency) from providers)";
		$result = $this->_db->executeQuery($query);
		if (!$result) {
		    echo 'Could not run query: ' . ' ' . mysql_error();
		    return;		    
		}

		$row = mysql_fetch_array($result);
		
		return $this->getProviderFromFetchRow($row); 
	}
	
	public function getNewestProviders() {
		global $ubi;
		
		$query = sprintf("SELECT * FROM providers ORDER BY ProviderId DESC LIMIT %d", $ubi['SITE']['NUMBER_NEWEST_POST']);
		$result = $this->_db->executeQuery($query);
		
		$providers = array();
		while ($row = mysql_fetch_assoc($result)) {					
			$providers[] = $this->getProviderFromFetchRow($row);
		}

		return $providers;		
	}
	
	public function getNumberOfProvider() {
		$query = "SELECT COUNT(*) FROM providers";
					
		$result = $this->_db->executeQuery($query);

		if (!$result) {
		    echo 'Could not run query: ' . ' ' . $query . ' ' . mysql_error();
		    return;
		}
		
		if ($row = mysql_fetch_row($result)) {						
			return $row[0];
		}
	}

//	public function getProviderNth($nthProvider) {		
//		$query = sprintf("SELECT * FROM providers LIMIT %d, 1", $nthProvider);
//		
//		$result = $this->_db->executeQuery($query);
//		
//		if (!$result) {
//			echo 'Could not run query: ' . ' ' . mysql_error();		    
//		}
//		
//		if ($row = mysql_fetch_assoc($result)) {
//			$provider = $this->getProviderFromFetchRow($row);
//								
//		}
//		
//		mysql_free_result($result);
//		
//		return $provider;
//	}
	
	public function getRandomProviders($nProvider) {
		$query = sprintf("SELECT * from providers ORDER BY RAND() limit %d", $nProvider);
		$result = $this->_db->executeQuery($query);
		
		$providers = array();
		while ($row = mysql_fetch_assoc($result)) {					
			$providers[] = $this->getProviderFromFetchRow($row);
		}

		return $providers;
	}
	
	public function searchProvidersByRegion($city, $type, $from, $offset) {
		if (!isset($city) || $city == '') {
			$city = '%%';
		}
		if (!is_numeric($city)) {
			$query = sprintf("SELECT * FROM addresses WHERE City LIKE '%s' AND Type = %d LIMIT %d, %d", 
				$city, $type, $from, $offset);
		} else {
			$query = sprintf("SELECT * FROM addresses JOIN providers ON providers.AddressId = addresses.AddressId " 
			. "AND %d IN(addresses.Location1, addresses.Location2, addresses.Location3) LIMIT %d, %d", 
			$region, $from, $offset);								
		}
		return $this->getProvidersFromSearchQuery($query, NULL);
	}
	
	/*public function searchAdvancedProviders($city, $district, $ward, $number, $street, $providerName) {
		$city = '%' . $city . '%';
		$district = '%' . $district . '%';
		$ward = '%' . $ward . '%';
		$number = '%' . $number . '%';
		$street = '%' . $street . '%';
		$providerName = '%' . $providerName . '%';
		
		$query = sprintf("SELECT AddressId FROM addresses " 
			. "WHERE City LIKE '%s' AND District LIKE '%s' AND Ward LIKE '%s' " 
			. "AND Number LIKE '%s' AND Street LIKE '%s'", $city, $district, $ward, $number, $street);
			
		
		return $this->getProvidersFromSearchQuery($query, $providerName);
	}*/
	
	private function getProvidersFromSearchQuery($query, $providerName) {
		if (!isset($providerName)) {
			$providerName = '%%';	
		}
		
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();
			return;		    
		}

		$addressIds = array();
		while ($row = mysql_fetch_assoc($result)) {					
			$addressIds[] = $row['AddressId'];
		}
		if (count($addressIds) > 0) {
			$strAddressId = '(';
			for ($i = 0; $i < count($addressIds) - 1; $i++) {
				$strAddressId .= "'" . $addressIds[$i] . "', ";
			}
			$strAddressId .= "'" . $addressIds[count($addressIds) - 1] . "')";
			$query2 = sprintf("SELECT * from providers WHERE Provider LIKE '%s' and AddressId IN %s", $providerName, $strAddressId);
			$result2 = $this->_db->executeQuery($query2);
			if ($result2) {
				$providers = array();
				while ($row = mysql_fetch_assoc($result2)) {					
					$providers[] = $this->getProviderFromFetchRow($row);
				}
				mysql_free_result($result2);	
			}
		}
		
		mysql_free_result($result);
		return $providers;
	}
	
	public function searchAdvancedProviders($city, $district, $ward, $number, $street, $providerName, $from, $offset) {
		global $ubi;
		
		if (!is_numeric($city)) {
			$city = '%' . $city . '%';
		}
		if (!is_numeric($district)) {
			$district = '%' . $district . '%';
		}
		if (!is_numeric($ward)) {
			$ward = '%' . $ward . '%';
		}
		
		$number = '%' . $number . '%';
		$street = '%' . $street . '%';
		$providerName = '%' . $providerName . '%';
		
		$prepareQuery = sprintf("SELECT AddressId FROM addresses " 
			. "WHERE Number LIKE '%s' AND Street LIKE '%s' AND Type=%d", $number, $street, $ubi['LOCATION']['PROVIDER']);
		if (is_numeric($city)) {
			$prepareQuery .= sprintf(" AND Location1 = %d", $city);
		}
		if (is_numeric($district)) {
			$prepareQuery .= sprintf(" AND Location2 = %d", $district);
		}
		if (is_numeric($ward)) {
			$prepareQuery .= sprintf(" AND Location3 = %d", $ward);
		}				
		$query = $prepareQuery . sprintf(" LIMIT %d, %d", $from, $offset);
		$result = $this->_db->executeQuery($query);
		
		if (!$result) {
			echo 'Could not run query: ' . ' ' . mysql_error();
			return;		    
		}

		$addressIds = array();
		while ($row = mysql_fetch_assoc($result)) {					
			$addressIds[] = $row['AddressId'];
		}
		if (count($addressIds) > 0) {
			$strAddressId = '(';
			for ($i = 0; $i < count($addressIds) - 1; $i++) {
				$strAddressId .= "'" . $addressIds[$i] . "', ";
			}
			$strAddressId .= "'" . $addressIds[count($addressIds) - 1] . "')";
			
			$query2 = sprintf("SELECT * from providers WHERE Provider LIKE '%s' and AddressId IN %s LIMIT %d, %d", 
				$providerName, $strAddressId, $from, $offset);
			$result2 = $this->_db->executeQuery($query2);
			if ($result2) {
				$providers = array();
				while ($row = mysql_fetch_assoc($result2)) {					
					$providers[] = $this->getProviderFromFetchRow($row);
				}
				mysql_free_result($result2);
				return $providers;	
			}
		}
		
		mysql_free_result($result);
	}
	
	public function updateUrlImage($providerID, $resourceID) {
		$query = sprintf("UPDATE providers SET MapResId = '%d' WHERE ProviderId = '%d'", $resourceID, $providerID);
		$this->_db->executeQuery($query);		
	}
	
	public function findProviderDTOsByLocationID($locationID) {
		$query = sprintf("SELECT * from providers, addresses, resources 
			WHERE providers.addressId = addresses.addressId 
			AND resources.resourceId = SUBSTRING_INDEX(providers.resourceList, ' ', 1)
			AND	'%d' IN (addresses.Location1, addresses.Location2, addresses.Location3)", $locationID);
		$result = $this->_db->executeQuery($query);
		$providers = array();
		while ($row = mysql_fetch_assoc($result)) {	
			$provider = ProviderDao::getProviderFromFetchRow($row);
			$add = AddressDao::getAddressFromFetchRow($row);
			$imgRes = ResourceDao::getResourceFromFetchRow($row);
			$providerDTO = new ProviderDTOModel($provider, $add, $imgRes);
			
			$providers[] = $providerDTO;
		}
		return $providers;
	}
	
	public static function getProviderFromFetchRow($row) {
		$provider = new ProviderModel();
		
		$provider->setProviderId($row['ProviderId']);
		$provider->setAddressId($row['AddressId']);
		$provider->setAnswerList($row['AnswerList']);
		$provider->setCategoryList($row['CategoryList']);
		$provider->setCellPhone($row['Cellphone']);
		$provider->setDescription($row['Description']);
		$provider->setProvider($row['Provider']);
		$provider->setResourceList($row['ResourceList']);
		$provider->setTelephone($row['Telephone']);
		$provider->setUserId($row['UserId']);
		$provider->setWebsite($row['Website']);
		$provider->setFrequency($row['Frequency']);
		$provider->setCreatedDate($row['CreatedDate']);
		$provider->setAccessedDate($row['AccessedDate']);
		$provider->setModifiedDate($row['ModifiedDate']);
		$provider->setTotalRate($row['TotalRate']);
		$provider->setPositiveRate($row['PositiveRate']);
		$provider->setNegativeRate($row['NegativeRate']);
		$provider->setMapResourceID($row['MapResId']);
		
		return $provider;
	}
}
?>